with wide_unsign_tmp2 as (--取出到件数据
    select *
    from (
             select substr(next_station_arrival_early_time, 1, 10)                                                       as date_time    --下一站最早到车时间
                  , next_station_arrival_early_time                                                                                      --下一站最早到车时间
                  , send_station_network_code                                                                                            --发件站点
                  , send_station_network_type                                                                                            --发件站点类型
                  , waybill_no                                                                                                           --运单号
                  , send_station_next_station                                                                                            --发件下一站(到件站点）
                  , send_station_next_statation_type                                                                                     --发件下一站站点类型
                  , send_station_number                                                                                                  --发件站点序列号
                  , is_void                                                                                                              --是否作废件
                  , is_refund                                                                                                            --是否退回件,1是,0否
                  , is_sign                                                                                                              --签收标识
                  , last_update_time                                                                                                     --运单最后更新时间
                  , pick_network_code                                                                                                    --寄件网点
                  , pick_network_id                                                                                                      --寄件网点
                  , end_network_network_code                                                                                             --末端网点
                  , deliver_early_scantime                                                                                               --最早派件时间
                  , deliver_later_scansitecode                                                                                           --最晚派件网点
                  , instore_early_scantime                                                                                               --第一次入库时间
                  , instore_later_scantime                                                                                               --最后一次入库时间
                  , instore_early_scansitecode                                                                                           --最后一次入库网点
                  , outstore_early_scantime                                                                                              --第一次出库时间
                  , outstore_early_scansitecode                                                                                          --第一次出库网点
                  , sign_early_scantime                                                                                                  --第一次签收时间
                  , sign_early_scansitecode                                                                                              --最后一次签收扫描网点
                  , customer_code
                  , customer_name
                  , if(instore_early_scantime is not null or instore_later_scantime is not null, 1,
                       0)                                                                                                as instore_flag --1代表入库操作,0代表未入库
                  , row_number()
                     over (partition by waybill_no, send_station_next_station order by next_station_arrival_early_time ) as rn
                  , input_time
                  , taking_early_scantime                                                                                as taking_time
             from jms_dwd.dwd_wide_unsign_trace_waybill_step_dt
             where dt >= date_add('{{ execution_date |  cst_ds }}', -30)
               and dt <= '{{ execution_date |  cst_ds }}'
               and substr(next_station_arrival_early_time, 1, 10) >= date_add('{{ execution_date |  cst_ds }}', -6)
               and substr(next_station_arrival_early_time, 1, 10) <= '{{ execution_date |  cst_ds }}'
               and waybill_no is not null
               and send_station_next_station is not null
               and send_station_next_statation_type = 2 --中心
               and customer_code is not null
         ) a
    where rn = 1
),
     waybill_incre as (
         select waybill_no --运单号
              , sender_province_id
              , sender_province_name
              , sender_city_id
              , sender_city_name
              , sender_area_id
              , sender_area_name
              , receiver_province_id
              , receiver_province_name
              , receiver_city_id
              , receiver_city_name
              , receiver_area_id
              , receiver_area_name
         from jms_dwd.dwd_yl_oms_oms_waybill_incre_dt
         where dt >= date_add('{{ execution_date |  cst_ds }}', -30)
           and dt <= '{{ execution_date |  cst_ds }}'
           and waybill_no is not null
     ),
     all_work_order as (--客诉量 ----------------------------------------------------------------------------------------
         select waybill_no
              , work_order_type as work_order_flag --客诉明细标识1普通工单 ,2理赔工单， 3项目工单 ,4邮政线下投诉
              , first_type_code
              , first_type_name
              , second_type_code
              , second_type_name
         from ( --1普通工单 ,2理赔工单， 3项目工单 ,4邮政线下投诉,5邮政申诉工单表,6邮政投诉工单表
                  select waybill_no
                       , work_order_type
                       , first_type_code
                       , first_type_name
                       , second_type_code
                       , second_type_name
                       , case
                             when work_order_type = 1 and first_type_code = 21 and registration_network_code = 888888
                                 then 1
                             when work_order_type = 3 and work_order_source = 1 then 2
                             when work_order_type = 2 or work_order_type = 4 then 3
                             else 0 end as flag --1普通工单问题类型为投诉,登记网点为总部 2平台工单工单来源为消费者  3邮政和理赔全部 0其他
                  from jms_dwd.dwd_complaint_work_order_detail_dt
                  where dt >= date_add('{{ execution_date |  cst_ds }}', -59)
                    and dt <= '{{ execution_date |  cst_ds }}'
                    and waybill_no is not null
--                     and (first_type_code is not null or first_type_name is not null or second_type_code is not null or
--                          second_type_name is not null)
--                     and (first_type_code <> '' or first_type_name <> '' or second_type_code <> '' or
--                          second_type_name <> '')
                  union all
                  select waybill_no
                       , 5    as work_order_type
                       , null as first_type_code
                       , null as first_type_name
                       , null as second_type_code
                       , null as second_type_name
                       , 0    as flag
                  from jms_dwd.dwd_appeal_info_dt --邮政申诉工单表
                  where dt >= date_add('{{ execution_date |  cst_ds }}', -59)
                    and dt <= '{{ execution_date |  cst_ds }}'
                    and waybill_no is not null
                  union all
                  select waybill_no
                       , 6    as work_order_type
                       , null as first_type_code
                       , null as first_type_name
                       , null as second_type_code
                       , null as second_type_name
                       , 0    as flag
                  from jms_dwd.dwd_complaint_info_dt --邮政投诉工单表
                  where dt >= date_add('{{ execution_date |  cst_ds }}', -59)
                    and dt <= '{{ execution_date |  cst_ds }}'
                    and waybill_no is not null
              ) a
         where flag <> 0
         group by waybill_no
                , work_order_type
                , first_type_code
                , first_type_name
                , second_type_code
                , second_type_name
     ),
     express_wo as (--破损量 ----------------------------------------------------------------------------------------
         select waybill_no
              , 'express' as express_flag --质量工单标识
              , problem_type_id           --问题件类型id（基础数据的一级类型id）
              , problem_type_code         --问题件类型编码
              , problem_type_name         --问题件类型名称
              , problem_type_subject_id   --问题件二级类型id（基础数据的二级类型id）
              , problem_type_subject_code --问题件二级类型编码
              , problem_type_subject_name --问题件二级类型名称
              , problem_description       --问题件原因
--               , work_order_status         --问题件流转状态 1:待处理  2:待审核  3:已审核  4:已驳回  5:已申诉 6:已结案
         from jms_dwd.dwd_sqs_problem_express_wo_base_dt --问题件工单清洗表
         where dt >= date_add('{{ execution_date |  cst_ds }}', -59)
           and dt <= '{{ execution_date |  cst_ds }}'
           and work_order_status = 6
           and problem_type_code = 27
           and waybill_no is not null
         group by waybill_no
                , problem_type_id           --问题件类型id（基础数据的一级类型id）
                , problem_type_code         --问题件类型编码
                , problem_type_name         --问题件类型名称
                , problem_type_subject_id   --问题件二级类型id（基础数据的二级类型id）
                , problem_type_subject_code --问题件二级类型编码
                , problem_type_subject_name --问题件二级类型名称
                , problem_description --问题件原因
--                 , work_order_status --问题件流转状态 1:待处理  2:待审核  3:已审核  4:已驳回  5:已申诉 6:已结案
     ),
     arbitration_type as (--丢失量
         select waybill_no
              , 'arbitration' as arbitration_flag --裁类标识
         from jms_dwd.dwd_arbitration_dt
         where dt >= date_add('{{ execution_date |  cst_ds }}', -44)
           and dt <= '{{ execution_date |  cst_ds }}'
           and status = 10
           and first_type_code = 03
           and waybill_no is not null
         group by waybill_no
     ),
     project_base as (
         select project_name --项目名称
              , code
         from jms_dim.dim_lmdm_sys_customer
         where project_name is not null
            or code is not null
         group by project_name
                , code
         union all
         select concat('菜鸟vip-', name) as project_name
              , code
         from jms_dim.dim_sys_customer_additional_base
         where customer_category = 6
           and name <> 'test'
           and (code is not null and name is not null)
         group by code
                , name
     ),
     market_signtimely_pp as (--时效配置表
         select project_name
              , origin_provice_id
              , origin_city_id
              , purpose_province_id
              , purpose_city_id
              , max(origin_provice_name)   as origin_provice_name
              , max(origin_city_name)      as origin_city_name
              , max(purpose_province_name) as purpose_province_name
              , max(purpose_city_name)     as purpose_city_name
              , max(timely_type)           as timely_type
              , max(city_t_timely)         as city_t_timely
         from jms_dim.dim_tab_dw_market_signtimely_base
         where timely_type = '省份-省份'
         group by project_name
                , origin_provice_id
                , origin_city_id
                , purpose_province_id
                , purpose_city_id
     ),
     market_signtimely_pc as (
         select project_name
              , origin_provice_id
              , origin_city_id
              , purpose_province_id
              , purpose_city_id
              , max(origin_provice_name)   as origin_provice_name
              , max(origin_city_name)      as origin_city_name
              , max(purpose_province_name) as purpose_province_name
              , max(purpose_city_name)     as purpose_city_name
              , max(timely_type)           as timely_type
              , max(city_t_timely)         as city_t_timely
         from jms_dim.dim_tab_dw_market_signtimely_base
         where timely_type = '省份-城市'
         group by project_name
                , origin_provice_id
                , origin_city_id
                , purpose_province_id
                , purpose_city_id
     ),
     market_signtimely_cp as (
         select project_name
              , origin_provice_id
              , origin_city_id
              , purpose_province_id
              , purpose_city_id
              , max(origin_provice_name)   as origin_provice_name
              , max(origin_city_name)      as origin_city_name
              , max(purpose_province_name) as purpose_province_name
              , max(purpose_city_name)     as purpose_city_name
              , max(timely_type)           as timely_type
              , max(city_t_timely)         as city_t_timely
         from jms_dim.dim_tab_dw_market_signtimely_base
         where timely_type = '城市-省份'
         group by project_name
                , origin_provice_id
                , origin_city_id
                , purpose_province_id
                , purpose_city_id
     ),
     market_signtimely_cc as (
         select project_name
              , origin_provice_id
              , origin_city_id
              , purpose_province_id
              , purpose_city_id
              , max(origin_provice_name)   as origin_provice_name
              , max(origin_city_name)      as origin_city_name
              , max(purpose_province_name) as purpose_province_name
              , max(purpose_city_name)     as purpose_city_name
              , max(timely_type)           as timely_type
              , max(city_t_timely)         as city_t_timely
         from jms_dim.dim_tab_dw_market_signtimely_base
         where timely_type = '城市-城市'
         group by project_name
                , origin_provice_id
                , origin_city_id
                , purpose_province_id
                , purpose_city_id
     ),
     result_table as (
         select t1.date_time                        as date_time                   --日期
              , t1.next_station_arrival_early_time  as scan_time                   --中心到件时间
              , t1.waybill_no                       as waybill_no                  --运单号
              , t1.customer_code                    as customer_code               --客户编码
              , t1.customer_name                    as customer_name               --客户名称
              , t3.project_name                     as project_name                --项目名称
              , t1.send_station_next_station        as site_code                   --中心到件站点
              , t7.name                             as site_name                   --中心名称
              , t7.agent_code                       as agent_code                  --代理区编码
              , t7.agent_name                       as agent_name                  --代理区名称
              , t1.send_station_next_statation_type as network_type                --中心到件站点类型
              , t1.last_update_time                 as last_update_time            --运单最后更新时间
              , t1.pick_network_code                as pick_network_code           --运单寄件网点
              , t1.deliver_early_scantime           as deliver_early_scantime      --最早派件时间
              , t1.deliver_later_scansitecode       as deliver_later_scansitecode  --最晚派件网点
              , t1.instore_early_scantime           as instore_early_scantime      --第一次入库时间
              , t1.instore_early_scansitecode       as instore_early_scansitecode  --最后一次入库网点
              , t1.outstore_early_scantime          as outstore_early_scantime     --第一次出库时间
              , t1.outstore_early_scansitecode      as outstore_early_scansitecode --第一次出库网点
              , t1.sign_early_scantime              as sign_early_scantime         --第一次签收时间
              , t1.sign_early_scansitecode          as sign_early_scansitecode     --最后一次签收扫描网点
              , t1.instore_flag                     as instore_flag                --1代表入库操作,0代表未入库
              , t1.is_refund                        as is_refund_flag              --是否退回件,1是,0否
              , t1.is_void                          as is_void_flag                --是否作废件 1是,0否
              , t1.is_sign                          as is_sign_flag                --签收标识 1正常签收2退件签收0未签收
              , if(t1.deliver_early_scantime is not null and t1.is_sign = 0, 1,
                   0)                               as del_war_flag                --派件标识,1代表派件未签收,0其他
              , if(datediff('{{ execution_date |  cst_ds }}', substr(t1.last_update_time, 1, 10)) > 7, 1,
                   0)                               as 7d_update_flag              --超过7天未跟新 1 是 0 否
              , t4.work_order_flag                  as work_order_flag             --客诉标识明细,6种
              , if(t4.waybill_no is not null, 1, 0) as work_order_join             --客诉标识,1代表客诉0代表非客诉
              , t4.first_type_code                  as first_type_code             --客诉一级问题类型编码
              , t4.first_type_name                  as first_type_name             --客诉一级问题类型
              , t4.second_type_code                 as second_type_code            --客诉二级问题类型编码
              , t4.second_type_name                 as second_type_name            --客诉二级问题类型
              , t5.arbitration_flag                 as arbitration_flag            --仲裁标识
              , if(t5.waybill_no is not null, 1, 0) as arbitration_join            --丢失标识 ,1代表丢失,0代表未丢失
              , t6.express_flag                     as express_flag                --质量工单标识
              , if(t6.waybill_no is not null, 1, 0) as express_join                --破损标识 1,代表破损,0未破损
              , t6.problem_type_id                  as problem_type_id             --破损问题件类型id（基础数据的一级类型id）
              , t6.problem_type_code                as problem_type_code           --破损问题件类型编码
              , t6.problem_type_name                as problem_type_name           --破损问题件类型名称
              , t6.problem_type_subject_id          as problem_type_subject_id     --破损问题件二级类型id（基础数据的二级类型id）
              , t6.problem_type_subject_code        as problem_type_subject_code   --破损问题件二级类型编码
              , t6.problem_type_subject_name        as problem_type_subject_name   --破损问题件二级类型名称
              , t6.problem_description              as problem_description         --破损问题件原因
              , t8.name                             as sign_early_scansitename     --签收网点名称
              , t1.date_time                        as dt                          --分区字段
              , t2.sender_province_id               as sender_province_id          --发件省id
              , t2.sender_province_name             as sender_province_name        --发件省name
              , t2.sender_city_id                   as sender_city_id              --发件市id
              , t2.sender_city_name                 as sender_city_name            --发件市name
              , t2.sender_area_id                   as sender_area_id              --发件区id
              , t2.sender_area_name                 as sender_area_name            --发件区name
              , t2.receiver_province_id             as receiver_province_id        --寄件省id
              , t2.receiver_province_name           as receiver_province_name      --寄件省name
              , t2.receiver_city_id                 as receiver_city_id            --寄件市id
              , t2.receiver_city_name               as receiver_city_name          --寄件市name
              , t2.receiver_area_id                 as receiver_area_id            --寄件区id
              , t2.receiver_area_name               as receiver_area_name          --寄件区name
              , t1.taking_time                      as taking_time                 --揽收时间
              , t1.input_time                       as input_time                  --运单录入时间
         from wide_unsign_tmp2 t1
                  left join waybill_incre t2
                            on t1.waybill_no = t2.waybill_no
                  left join project_base t3
                            on t1.customer_code = t3.code
                  left join all_work_order t4
                            on t1.waybill_no = t4.waybill_no
                  left join arbitration_type t5
                            on t1.waybill_no = t5.waybill_no
                  left join express_wo t6
                            on t1.waybill_no = t6.waybill_no
                  left join jms_dim.dim_network_whole_massage t7
                            on t1.send_station_next_station = t7.code
                  left join jms_dim.dim_network_whole_massage t8
                            on t1.sign_early_scansitecode = t8.code
     )
insert
overwrite
table
jms_dm.dm_mng_customer_daliy_center_waybill_detail_dt
partition
(
dt
)
select t1.date_time                                                                      --日期
     , t1.scan_time                                                                      --中心到件时间
     , t1.waybill_no                                                                     --运单号
     , t1.customer_code                                                                  --客户编码
     , t1.customer_name                                                                  --客户名称
     , t1.project_name                                                                   --项目名称
     , t1.site_code                                                                      --中心到件站点
     , t1.site_name                                                                      --中心名称
     , t1.agent_code                                                                     --代理区编码
     , t1.agent_name                                                                     --代理区名称
     , t1.network_type                                                                   --中心到件站点类型
     , t1.last_update_time                                                               --运单最后更新时间
     , t1.pick_network_code                                                              --运单寄件网点
     , t1.deliver_early_scantime                                                         --最早派件时间
     , t1.deliver_later_scansitecode                                                     --最晚派件网点
     , t1.instore_early_scantime                                                         --第一次入库时间
     , t1.instore_early_scansitecode                                                     --最后一次入库网点
     , t1.outstore_early_scantime                                                        --第一次出库时间
     , t1.outstore_early_scansitecode                                                    --第一次出库网点
     , t1.sign_early_scantime                                                            --第一次签收时间
     , t1.sign_early_scansitecode                                                        --最后一次签收扫描网点
     , t1.instore_flag                                                                   --1代表入库操作,0代表未入库
     , t1.is_refund_flag                                                                 --是否退回件,1是,0否
     , t1.is_void_flag                                                                   --是否作废件 1是,0否
     , t1.is_sign_flag                                                                   --签收标识 1正常签收2退件签收0未签收
     , t1.del_war_flag                                                                   --派件标识,1代表派件未签收,0其他
     , t1.7d_update_flag                                                                 --超过7天未跟新 1 是 0 否
     , t1.work_order_flag                                                                --客诉标识明细,6种
     , t1.work_order_join                                                                --客诉标识,1代表客诉0代表非客诉
     , t1.first_type_code                                                                --客诉一级问题类型编码
     , t1.first_type_name                                                                --客诉一级问题类型
     , t1.second_type_code                                                               --客诉二级问题类型编码
     , t1.second_type_name                                                               --客诉二级问题类型
     , t1.arbitration_flag                                                               --仲裁标识
     , t1.arbitration_join                                                               --丢失标识 ,1代表丢失,0代表未丢失
     , t1.express_flag                                                                   --质量工单标识
     , t1.express_join                                                                   --破损标识 1,代表破损,0未破损
     , t1.problem_type_id                                                                --破损问题件类型id（基础数据的一级类型id）
     , t1.problem_type_code                                                              --破损问题件类型编码
     , t1.problem_type_name                                                              --破损问题件类型名称
     , t1.problem_type_subject_id                                                        --破损问题件二级类型id（基础数据的二级类型id）
     , t1.problem_type_subject_code                                                      --破损问题件二级类型编码
     , t1.problem_type_subject_name                                                      --破损问题件二级类型名称
     , t1.problem_description                                                            --破损问题件原因
     , t1.sign_early_scansitename                                                        --签收网点名称
     , t1.sender_province_id                                                             --发件省id
     , t1.sender_province_name                                                           --发件省name
     , t1.sender_city_id                                                                 --发件市id
     , t1.sender_city_name                                                               --发件市name
     , t1.sender_area_id                                                                 --发件区id
     , t1.sender_area_name                                                               --发件区name
     , t1.receiver_province_id                                                           --寄件省id
     , t1.receiver_province_name                                                         --寄件省name
     , t1.receiver_city_id                                                               --寄件市id
     , t1.receiver_city_name                                                             --寄件市name
     , t1.receiver_area_id                                                               --寄件区id
     , t1.receiver_area_name                                                             --寄件区name
     , instore_early_scantime                                      as in_store_scan_time
     , coalesce(t1.taking_time, t1.input_time)                     as start_time
     , coalesce(t1.instore_early_scantime, t1.sign_early_scantime) as end_time
     , datediff(coalesce(t1.instore_early_scantime, t1.sign_early_scantime),
                coalesce(t1.taking_time, t1.input_time))           as diff_day
     , t2.city_t_timely                                            as pp_city_t_timely   --省省时效天数
     , t3.city_t_timely                                            as pc_city_t_timely   --省市时效天数
     , t4.city_t_timely                                            as cp_city_t_timely   --市省时效天数
     , t5.city_t_timely                                            as cc_city_t_timely   --市市时效天数
     , greatest(t2.city_t_timely, t3.city_t_timely, t4.city_t_timely,
                t5.city_t_timely)                                  as need_city_t_timely --时效天数
     , t1.dt                                                                             --分区字段
from result_table t1
         left join market_signtimely_pp t2
                   on t1.project_name = t2.project_name
                       and t1.sender_province_id = t2.origin_provice_id
                       and t1.receiver_province_id = t2.purpose_province_id
         left join market_signtimely_pc t3
                   on t1.project_name = t3.project_name
                       and t1.sender_province_id = t3.origin_provice_id
                       and t1.receiver_province_id = t3.purpose_province_id
                       and t1.receiver_city_id = t3.purpose_city_id
         left join market_signtimely_cp t4
                   on t1.project_name = t4.project_name
                       and t1.sender_province_id = t4.origin_provice_id
                       and t1.receiver_province_id = t4.purpose_province_id
                       and t1.sender_city_id = t4.origin_city_id
         left join market_signtimely_cc t5
                   on t1.project_name = t5.project_name
                       and t1.sender_province_id = t5.origin_provice_id
                       and t1.receiver_province_id = t5.purpose_province_id
                       and t1.receiver_city_id = t5.purpose_city_id
                       and t1.sender_city_id = t5.origin_city_id
where (t1.project_name is not null or t1.project_name <> '')
    
 distribute by pmod(hash(rand()), 10)
 ;